/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package models;

import Utils.DBConnection;
import Utils.Settings;
import Utils.Utils;
import com.mysql.jdbc.Util;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author user
 */
public class ProductModel {

    DBConnection db = new DBConnection(Settings.getDb(), Settings.getDbLogin(), Settings.getDbPassword());
    String message;

    public int GetProducts_Count(int categoryID) {
        try {
            ResultSet rs = db.ExecuteQuery("SELECT COUNT(*) AS cnt FROM products where CategoryID=" + categoryID + " or " + categoryID + "=-1");

            if (!rs.next()) {
                return -1;
            }
            return rs.getInt("cnt");
        } catch (Exception ex) {
            return -1;
        } finally {
            //  db.close();
        }
    }

    public HashMap GetProduct(int productID) {
        try {
            String querry = "SELECT * FROM products "
                    + " where ProductID=" + productID + " ";

            ResultSet rs = db.ExecuteQuery(querry);
            ArrayList Rows = Utils.resultSetToArrayList(rs);
            HashMap Row = (HashMap) Rows.get(0);
            return Row;
        } catch (Exception ex) {
            return null;
        } finally {
            //  db.close();
        }
    }

    public ArrayList GetProducts(int offset, int pageSize, String sort, String sortDir, int categoryID) {
        try {
            String querry = "SELECT a.*,b.Name AS 'CategoryName', b.CategoryID FROM products a"
                    + " LEFT JOIN categories b"
                    + " ON a.CategoryID = b.CategoryID "
                    + " where a.CategoryID=" + categoryID + " or " + categoryID + "=-1"
                    + " AND Active=1"
                    + " order by " + sort + " " + sortDir
                    + " LIMIT " + offset + "," + pageSize
                    + " ";

            ResultSet rs = db.ExecuteQuery(querry);
            ArrayList Rows = Utils.resultSetToArrayList(rs);
            return Rows;
        } catch (Exception ex) {
            return null;
        } finally {
            //  db.close();
        }
    }

    public int UpdateProduct(int productID, String name, String description, double price, int categoryID, double quantity) {
        try {

            String query = "UPDATE products SET "
                    + "`Name` = \"" + name + "\","
                    + "Description = \"" + description + "\","
                    + "Price = " + Double.toString(price) + ","
                    + "CategoryID = " + categoryID + ","
                    + "Quantity = " + Double.toString(quantity) + " "
                    + "WHERE ProductID=" + productID + " ";

            int rows = db.ExecuteUpdate(query);

            if (rows > 0) {
                message = "Produsul a fost actualizat cu succes!";
                return 1;
            } else {
                message = "Produsul nu a fost actualizat!";
                return -1;
            }

        } catch (Exception ex) {
            message = "Eroare cod 001";
            return -1;
        } finally {
            //     db.close();
        }
    }

    public int AddProduct(String name, String description, double price, int categoryID, double quantity) {
        try {

            String query = "INSERT INTO products (`Name`,Description,Price,CategoryID,Quantity,Photo)"
                    + "VALUES (\"" + name + "\",\"" + description + "\"," + price + "," + categoryID + "," + quantity + ",\"\")";

            int rows = db.ExecuteUpdate(query);

            if (rows > 0) {
                message = "Produsul a fost actualizat cu succes!";
                return 1;
            } else {
                message = "Produsul nu a fost actualizat!";
                return -1;
            }

        } catch (Exception ex) {
            message = "Eroare cod 001";
            return -1;
        } finally {
            //     db.close();
        }
    }

    public int ActivateProduct(int productID, int active) {
        try {

            String query = "UPDATE products "
                    + "SET Active=" + active + " "
                    + "WHERE ProductID=" + productID + " ";

            int rows = db.ExecuteUpdate(query);

            if (rows > 0) {
                message = "Produsul a fost sters cu succes!";
                return 1;
            } else {
                message = "Produsul nu a fost sters!";
                return -1;
            }

        } catch (Exception ex) {
            message = "Eroare cod 001";
            return -1;
        } finally {
            //     db.close();
        }
    }

    public String GetMessage() {
        return message;
    }
}
